#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
MySQL数据库操作工具
为韶华墨韵项目提供基本的数据库操作功能
"""

import mysql.connector
from mysql.connector import Error
import sys
import json
from datetime import datetime

# 数据库连接配置
DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'database': 'shufa',
    'user': 'root',
    'password': 'root',
    'charset': 'utf8mb4',
    'use_unicode': True,
    'autocommit': False,  # 手动控制事务
    'connect_timeout': 30
}

class MySQLOperator:
    """
    MySQL数据库操作类
    """
    
    def __init__(self):
        self.connection = None
        self.cursor = None
    
    def connect(self):
        """
        连接数据库
        """
        try:
            self.connection = mysql.connector.connect(**DB_CONFIG)
            self.cursor = self.connection.cursor(dictionary=True)
            print(f"✅ 已连接到数据库: {DB_CONFIG['database']}")
            return True
        except Error as e:
            print(f"❌ 数据库连接失败: {e}")
            return False
    
    def disconnect(self):
        """
        断开数据库连接
        """
        if self.cursor:
            self.cursor.close()
        if self.connection and self.connection.is_connected():
            self.connection.close()
            print("🔌 数据库连接已关闭")
    
    def execute_query(self, query, params=None, fetch_all=True):
        """
        执行查询语句
        
        Args:
            query (str): SQL查询语句
            params (tuple): 查询参数
            fetch_all (bool): 是否获取所有结果
        
        Returns:
            list/dict: 查询结果
        """
        try:
            if not self.connection or not self.connection.is_connected():
                if not self.connect():
                    return None
            
            self.cursor.execute(query, params or ())
            
            if fetch_all:
                result = self.cursor.fetchall()
            else:
                result = self.cursor.fetchone()
            
            return result
            
        except Error as e:
            print(f"❌ 查询执行失败: {e}")
            return None
    
    def execute_update(self, query, params=None, commit=True):
        """
        执行更新语句（INSERT, UPDATE, DELETE）
        
        Args:
            query (str): SQL更新语句
            params (tuple): 更新参数
            commit (bool): 是否自动提交
        
        Returns:
            int: 受影响的行数
        """
        try:
            if not self.connection or not self.connection.is_connected():
                if not self.connect():
                    return 0
            
            self.cursor.execute(query, params or ())
            affected_rows = self.cursor.rowcount
            
            if commit:
                self.connection.commit()
                print(f"✅ 操作成功，影响 {affected_rows} 行")
            
            return affected_rows
            
        except Error as e:
            print(f"❌ 更新执行失败: {e}")
            if self.connection:
                self.connection.rollback()
            return 0
    
    def show_tables(self):
        """
        显示所有表
        """
        result = self.execute_query("SHOW TABLES")
        if result:
            print("\n📋 数据库表列表:")
            for i, table in enumerate(result, 1):
                table_name = list(table.values())[0]
                print(f"  {i:2d}. {table_name}")
        return result
    
    def describe_table(self, table_name):
        """
        显示表结构
        
        Args:
            table_name (str): 表名
        """
        result = self.execute_query(f"DESCRIBE {table_name}")
        if result:
            print(f"\n📊 表 '{table_name}' 结构:")
            print(f"{'字段名':<20} {'类型':<20} {'空值':<8} {'键':<8} {'默认值':<15} {'额外':<15}")
            print("-" * 90)
            for row in result:
                print(f"{row['Field']:<20} {row['Type']:<20} {row['Null']:<8} {row['Key']:<8} {str(row['Default']):<15} {row['Extra']:<15}")
        return result
    
    def count_records(self, table_name, condition=None):
        """
        统计表记录数
        
        Args:
            table_name (str): 表名
            condition (str): WHERE条件
        
        Returns:
            int: 记录数
        """
        query = f"SELECT COUNT(*) as count FROM {table_name}"
        if condition:
            query += f" WHERE {condition}"
        
        result = self.execute_query(query, fetch_all=False)
        if result:
            count = result['count']
            print(f"📊 表 '{table_name}' 记录数: {count}")
            return count
        return 0
    
    def select_data(self, table_name, columns="*", condition=None, limit=None, order_by=None):
        """
        查询数据
        
        Args:
            table_name (str): 表名
            columns (str): 查询字段
            condition (str): WHERE条件
            limit (int): 限制条数
            order_by (str): 排序字段
        
        Returns:
            list: 查询结果
        """
        query = f"SELECT {columns} FROM {table_name}"
        
        if condition:
            query += f" WHERE {condition}"
        
        if order_by:
            query += f" ORDER BY {order_by}"
        
        if limit:
            query += f" LIMIT {limit}"
        
        result = self.execute_query(query)
        if result:
            print(f"\n📋 查询结果 (共 {len(result)} 条):")
            if result:
                # 显示表头
                headers = list(result[0].keys())
                header_line = " | ".join(f"{h:<15}" for h in headers)
                print(header_line)
                print("-" * len(header_line))
                
                # 显示数据
                for row in result[:10]:  # 最多显示10条
                    row_line = " | ".join(f"{str(v):<15}" for v in row.values())
                    print(row_line)
                
                if len(result) > 10:
                    print(f"... 还有 {len(result) - 10} 条记录")
        
        return result
    
    def backup_table(self, table_name, backup_file=None):
        """
        备份表数据到JSON文件
        
        Args:
            table_name (str): 表名
            backup_file (str): 备份文件路径
        """
        if not backup_file:
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            backup_file = f"backup_{table_name}_{timestamp}.json"
        
        result = self.execute_query(f"SELECT * FROM {table_name}")
        if result:
            # 处理datetime对象
            for row in result:
                for key, value in row.items():
                    if isinstance(value, datetime):
                        row[key] = value.isoformat()
            
            with open(backup_file, 'w', encoding='utf-8') as f:
                json.dump(result, f, ensure_ascii=False, indent=2)
            
            print(f"✅ 表 '{table_name}' 已备份到: {backup_file}")
            print(f"📊 备份记录数: {len(result)}")
        
        return backup_file
    
    def get_system_info(self):
        """
        获取系统信息
        """
        print("\n🔍 系统信息:")
        
        # 若依核心表统计
        core_tables = {
            'sys_user': '用户表',
            'sys_role': '角色表', 
            'sys_menu': '菜单表',
            'sys_dept': '部门表',
            'sys_config': '配置表',
            'sys_dict_type': '字典类型表',
            'sys_dict_data': '字典数据表'
        }
        
        for table, desc in core_tables.items():
            count = self.count_records(table)
            print(f"  {desc}: {count} 条记录")
        
        # 业务表统计
        business_tables = {
            'works': '作品表',
            'competition': '比赛表',
            'comment': '评论表'
        }
        
        print("\n📊 业务表统计:")
        for table, desc in business_tables.items():
            try:
                count = self.count_records(table)
                print(f"  {desc}: {count} 条记录")
            except:
                print(f"  {desc}: 表不存在")

def main():
    """
    主函数 - 交互式操作
    """
    db = MySQLOperator()
    
    if not db.connect():
        return
    
    try:
        while True:
            print("\n" + "="*50)
            print("韶华墨韵 - MySQL数据库操作工具")
            print("="*50)
            print("1. 显示所有表")
            print("2. 查看表结构")
            print("3. 统计表记录")
            print("4. 查询数据")
            print("5. 备份表数据")
            print("6. 系统信息")
            print("7. 执行自定义SQL")
            print("0. 退出")
            print("-"*50)
            
            choice = input("请选择操作 (0-7): ").strip()
            
            if choice == '0':
                break
            elif choice == '1':
                db.show_tables()
            elif choice == '2':
                table_name = input("请输入表名: ").strip()
                if table_name:
                    db.describe_table(table_name)
            elif choice == '3':
                table_name = input("请输入表名: ").strip()
                if table_name:
                    condition = input("请输入WHERE条件 (可选): ").strip()
                    db.count_records(table_name, condition if condition else None)
            elif choice == '4':
                table_name = input("请输入表名: ").strip()
                if table_name:
                    columns = input("请输入查询字段 (默认*): ").strip() or "*"
                    condition = input("请输入WHERE条件 (可选): ").strip()
                    limit = input("请输入限制条数 (可选): ").strip()
                    order_by = input("请输入排序字段 (可选): ").strip()
                    
                    db.select_data(
                        table_name, 
                        columns, 
                        condition if condition else None,
                        int(limit) if limit.isdigit() else None,
                        order_by if order_by else None
                    )
            elif choice == '5':
                table_name = input("请输入表名: ").strip()
                if table_name:
                    backup_file = input("请输入备份文件名 (可选): ").strip()
                    db.backup_table(table_name, backup_file if backup_file else None)
            elif choice == '6':
                db.get_system_info()
            elif choice == '7':
                sql = input("请输入SQL语句: ").strip()
                if sql:
                    if sql.upper().startswith(('SELECT', 'SHOW', 'DESCRIBE', 'EXPLAIN')):
                        result = db.execute_query(sql)
                        if result:
                            print(f"\n查询结果 (共 {len(result)} 条):")
                            for i, row in enumerate(result[:5], 1):
                                print(f"{i}. {row}")
                            if len(result) > 5:
                                print(f"... 还有 {len(result) - 5} 条记录")
                    else:
                        confirm = input("⚠️  这是一个修改操作，确认执行吗？(y/N): ")
                        if confirm.lower() == 'y':
                            db.execute_update(sql)
                        else:
                            print("操作已取消")
            else:
                print("❌ 无效选择，请重新输入")
    
    except KeyboardInterrupt:
        print("\n\n👋 用户中断操作")
    except Exception as e:
        print(f"\n❌ 发生错误: {e}")
    finally:
        db.disconnect()

if __name__ == "__main__":
    main()